﻿using LBSoft.Common.DotNetCode;
using LBSoft.Common.DotNetExt;
using LBSoft.DataBase;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace LBSoft.Business
{
    public class Common_Dal
    {
        protected IDbHelper helper = DataFactory.SqlDataBase();

        public Common_Dal()
        {

        }

        public Common_Dal(string connectstring)
        {
            helper = DataFactory.SqlDataBase(connectstring);
        }

        /// <summary>
        /// 通过sql获取数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySQL(StringBuilder sql)
        {
            return helper.GetDataSetBySQL(sql, null);
        }

        /// <summary>
        /// 通过sql获取数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySQL(StringBuilder sql)
        {
            return helper.GetDataTableBySQL(sql, null);
        }

        /// <summary>
        /// 通过sql获取数据表,有参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySQL(StringBuilder sql, SqlParam[] param)
        {
            return helper.GetDataTableBySQL(sql, param);
        }

        /// <summary>
        /// 通过sql获取数据表列名大写
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySQLColToUpper(StringBuilder sql)
        {
            return helper.GetDataTableBySQLColToUpper(sql, null);
        }

        /// <summary>
        /// 通过sql获取数据表列名大写,有参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySQLColToUpper(StringBuilder sql, SqlParam[] param)
        {
            return helper.GetDataTableBySQLColToUpper(sql, param);
        }

        /// <summary>
        /// 分页获取数据，自定义排序
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="cols"></param>
        /// <param name="SqlWhere"></param>
        /// <param name="order"></param>
        /// <param name="IList_param"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetListByPage(string tabName, string cols, StringBuilder SqlWhere, string order, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT " + cols + " from " + tabName);
            strSql.Append(SqlWhere);
            return helper.GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), order, pageIndex, pageSize, ref count);
        }

        /// <summary>
        /// 分页获取数据，默认按创建时间排序
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="SqlWhere"></param>
        /// <param name="IList_param"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetListByPage(string tabName, StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * from " + tabName);
            strSql.Append(SqlWhere);
            return helper.GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "CreateTime", "Desc", pageIndex, pageSize, ref count);
        }

        /// <summary>
        /// 提交数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <param name="pkVal"></param>
        /// <param name="ht"></param>
        /// <returns></returns>
        public bool SubmitData(string tableName, string pkName, string pkVal, Hashtable ht)
        {
            return helper.Submit_AddOrEdit(tableName, pkName, pkVal, ht);
        }

        /// <summary>
        /// 通过ID获取单行hashtable
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <param name="pkVal"></param>
        /// <returns></returns>
        public Hashtable GetDataByID(string tableName, string pkName, string pkVal)
        {
            return helper.GetHashtableById(tableName, pkName, pkVal);
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <param name="pkValues"></param>
        /// <returns></returns>
        public int BatchDeleteData(string tableName, string pkName, object[] pkValues)
        {
            return helper.BatchDeleteData(tableName, pkName, pkValues);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <param name="pkVal"></param>
        /// <returns></returns>
        public int DeleteData(string tableName, string pkName, string pkVal)
        {
            return helper.DeleteData(tableName, pkName, pkVal);
        }

        /// <summary>
        /// 逻辑删除
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <param name="pkVal"></param>
        /// <returns></returns>
        public int UpdateDeleteData(string tableName, string pkName, string pkVal)
        {
            return helper.UpdateDeleteData(tableName, pkName, pkVal);
        }

        /// <summary>
        /// 批量逻辑删除
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="pkName"></param>
        /// <param name="pkValues"></param>
        /// <returns></returns>
        public int BatchUpdateDeleteData(string tableName, string pkName, object[] pkValues)
        {
            return helper.BatchUpdateDeleteData(tableName, pkName, pkValues);
        }

        /// <summary>
        /// 自动获取前台传入的参数，添加到hash表中
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="pkName"></param>
        /// <param name="con"></param>
        /// <returns></returns>
        public Hashtable AddHashtable(string tabName, string pkName, HttpContext con)
        {
            Hashtable ht = new Hashtable();

            StringBuilder sql = new StringBuilder("SELECT * FROM " + tabName + " WHERE " + pkName + "='" + con.Request[pkName] + "'");
            DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql);
            if (dt != null)
            {
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dt.Rows.Count > 0)
                    {
                        if (!ht.ContainsKey(dc.ColumnName))
                            ht.Add(dc.ColumnName, dt.Rows[0][dc.ColumnName]);
                    }

                    if (ht.ContainsKey(dc.ColumnName))
                    {

                        if (string.IsNullOrEmpty(con.Request[dc.ColumnName]))
                        {
                            ht[dc.ColumnName] = DBNull.Value;
                        }
                        else
                        {
                            ht[dc.ColumnName] = con.Request[dc.ColumnName].ToString2().Trim();//原
                        }

                    }
                    else
                    {

                        if (string.IsNullOrEmpty(con.Request[dc.ColumnName]))
                        {
                            ht.Add(dc.ColumnName, DBNull.Value);
                        }
                        else
                        {
                            ht.Add(dc.ColumnName, con.Request[dc.ColumnName].ToString2().Trim());
                        }

                    }

                }
            }
            if (string.IsNullOrEmpty(con.Request[pkName]))
            {
                ht[pkName] = Guid.NewGuid().ToString();

            }
            else
            {
                ht[pkName] = con.Request[pkName].ToString2().Trim();
            }
            return ht;
        }

        /// <summary>
        /// 自动获取前台传入的参数，添加到hash表中
        /// </summary>
        /// <param name="tabName"></param>
        /// <param name="con"></param>
        /// <returns></returns>
        public Hashtable AddHashtable(string tabName, HttpContext con)
        {
            return AddHashtable(tabName, "GUID", con);
        }

        /// <summary>
        /// 执行脚本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteBySql(StringBuilder sql)
        {
            return helper.ExecuteBySql(sql);
        }

        /// <summary>
        /// 执行脚本,有参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int ExecuteBySql(StringBuilder sql, SqlParam[] param)
        {
            return helper.ExecuteBySql(sql, param);
        }

        /// <summary>
        /// 批量执行脚本,有参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int BatchExecuteBySql(StringBuilder[] sql, object[] param)
        {
            return helper.BatchExecuteBySql(sql, param);
        }

        /// <summary>
        /// 执行脚本
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            return helper.GetObjectValue(new StringBuilder(sql));
        }

        /// <summary>
        /// 数据表批量插入
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public bool SqlBulkCopyImport(DataTable dt)
        {
            return helper.SqlBulkCopyImport(dt);
        }

        /// <summary>
        /// 当前月份表
        /// </summary>
        /// <param name="tableName">原始表名</param>
        /// <returns></returns>
        public string GetCurrentMonthTableName(string tableName)
        {
            return tableName + System.DateTime.Now.ToString("yyyyMM").ToString2();
        }

        /// <summary>
        /// 当前年份表
        /// </summary>
        /// <param name="tableName">原始表名</param>
        /// <returns></returns>
        public string GetCurrentYearTableName(string tableName)
        {
            return tableName + System.DateTime.Now.ToString("yyyy").ToString2();
        }
    }
}
